﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace GestorDeFlotas.Core
{
    public class Rendicion
    {
        public DateTime Fecha { get; set; }

        public Chofer Chofer { get; set; }

        public Turno Turno { get; set; }

        public decimal Importe { get; set; }

        public Rendicion(DateTime fecha, Chofer chofer, Turno turno, decimal importe)
        {
            this.Fecha = fecha;
            this.Chofer = chofer;
            this.Turno = turno;
            this.Importe = importe;
        }

        public void Rendir()
        {
            using (SqlConnection connection = SqlUtils.GetConnection())
            {
                connection.Open();
                SqlTransaction transaction = connection.BeginTransaction();

                try
                {
                    int idRendicion = CrearRendicion(connection, transaction);
                    InsertarItemsRendicion(idRendicion, connection, transaction);
                    ActualizarViajesRendidos(connection, transaction);
                    transaction.Commit();
                }
                catch (Exception)
                {
                    transaction.Rollback();
                    throw;
                }
            }
        }

        private void ActualizarViajesRendidos(SqlConnection connection, SqlTransaction transaction)
        {
            string sql = "UPDATE [CRAZY_TAXI].[TL_VIAJES] " +
                                        "SET [via_rendido] = 1 " +
                                        "FROM [CRAZY_TAXI].[TL_VIAJES] " +
                                        "WHERE (viacho_id = @Chofer) AND (viatur_id = @Turno) " +
                                        "AND DATEADD(dd, 0, DATEDIFF(dd, 0, via_fecha)) = DATEADD(dd, 0, DATEDIFF(dd, 0, @Fecha)) ";

            SqlCommand insertarItemsFactura = SqlUtils.GetNewCommand(sql);

            SqlParameter paramChofer = new SqlParameter("@Chofer", SqlDbType.Int);
            paramChofer.IsNullable = false;
            paramChofer.Value = this.Chofer.Id;

            SqlParameter paramFecha = new SqlParameter("@Fecha", SqlDbType.DateTime);
            paramFecha.IsNullable = false;
            paramFecha.Value = this.Fecha;

            SqlParameter paramTurno = new SqlParameter("@Turno", SqlDbType.Int);
            paramTurno.IsNullable = false;
            paramTurno.Value = this.Turno.Id;

            insertarItemsFactura.Parameters.Add(paramChofer);
            insertarItemsFactura.Parameters.Add(paramFecha);
            insertarItemsFactura.Parameters.Add(paramTurno);
            insertarItemsFactura.Connection = connection;
            insertarItemsFactura.Transaction = transaction;

            insertarItemsFactura.ExecuteNonQuery();
        }

        private void InsertarItemsRendicion(int idRendicion, SqlConnection connection, SqlTransaction transaction)
        {
            string sql = "INSERT INTO [CRAZY_TAXI].[TL_ItemsRendicion] ([itrvia_id],[itrren_id],[itr_importe]) " +
                                           "SELECT via_id, " + idRendicion.ToString() + ", (tur_valor_bandera + (tur_valor_ficha * via_cant_fichas)) " +
                                           "FROM [CRAZY_TAXI].[TL_VIAJES] " +
                                           "INNER JOIN [CRAZY_TAXI].[TL_TURNOS] AS T ON tur_id = viatur_id " +
                                           "WHERE DATEADD(dd, 0, DATEDIFF(dd, 0, via_fecha)) = DATEADD(dd, 0, DATEDIFF(dd, 0, @Fecha)) " +
                                           "AND viatur_id = @Turno " +
                                           "AND viacho_id = @Chofer ";

            SqlCommand insertarItemsFactura = SqlUtils.GetNewCommand(sql);

            SqlParameter paramChofer = new SqlParameter("@Chofer", SqlDbType.Int);
            paramChofer.IsNullable = false;
            paramChofer.Value = this.Chofer.Id;

            SqlParameter paramFecha = new SqlParameter("@Fecha", SqlDbType.DateTime);
            paramFecha.IsNullable = false;
            paramFecha.Value = this.Fecha;

            SqlParameter paramTurno = new SqlParameter("@Turno", SqlDbType.Int);
            paramTurno.IsNullable = false;
            paramTurno.Value = this.Turno.Id;

            insertarItemsFactura.Parameters.Add(paramChofer);
            insertarItemsFactura.Parameters.Add(paramFecha);
            insertarItemsFactura.Parameters.Add(paramTurno);
            insertarItemsFactura.Connection = connection;
            insertarItemsFactura.Transaction = transaction;

            insertarItemsFactura.ExecuteNonQuery();
        }

        private int CrearRendicion(SqlConnection connection, SqlTransaction transaction)
        {
            string sql = "INSERT INTO [CRAZY_TAXI].[TL_Rendiciones] ([ren_fecha], [rencho_id], [rentur_id], [ren_importe]) " +
                "OUTPUT INSERTED.ren_id " +
                "VALUES (@Fecha, @IdChofer, @IdTurno, @Importe)";

            SqlCommand crearRendicion = SqlUtils.GetNewCommand(sql);

            SqlParameter paramChofer = new SqlParameter("@IdChofer", SqlDbType.Int);
            paramChofer.IsNullable = false;
            paramChofer.Value = this.Chofer.Id;

            SqlParameter paramImporte = new SqlParameter("@Importe", SqlDbType.Decimal);
            paramImporte.IsNullable = false;
            paramImporte.Value = this.Importe;

            SqlParameter paramFecha = new SqlParameter("@Fecha", SqlDbType.DateTime);
            paramFecha.IsNullable = false;
            paramFecha.Value = this.Fecha;

            SqlParameter paramTurno = new SqlParameter("@IdTurno", SqlDbType.Int);
            paramTurno.IsNullable = false;
            paramTurno.Value = this.Turno.Id;

            crearRendicion.Parameters.Add(paramChofer);
            crearRendicion.Parameters.Add(paramImporte);
            crearRendicion.Parameters.Add(paramFecha);
            crearRendicion.Parameters.Add(paramTurno);
            crearRendicion.Connection = connection;
            crearRendicion.Transaction = transaction;
            
            return Convert.ToInt32(crearRendicion.ExecuteScalar());
        }
    }
}
